﻿using Anke.SHManage.Model.BasicEventInfo;
using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    public class FixedAssetsScrapDAL
    {
        /// <summary>
        /// 获取固定资产报废申请单列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="assetTypeID"></param>
        /// <param name="materialID"></param>
        /// <param name="assetID"></param>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public object GetScrapList(int pageSize, int pageIndex, string assetTypeID, string materialID, string assetID, DateTime DateStart, DateTime DateEnd)
        {
            List<FIXED_ScrapInfo> list = new List<FIXED_ScrapInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct identity(int,1,1) as 行号 ");
            sb.Append(" ,fs.编码,fs.资产编码,fs.申请科室,fs.申请日期 ");
            sb.Append(" ,fs.生产国,fs.原值,fs.财政购建资金,fs.非财政购建资金 ");
            sb.Append(" ,fs.累计折旧,fs.申请理由,fs.申请科室负责人 ");
            sb.Append(" ,资产名称=fm.资产名称,固定资产编号=fm.固定资产编码,规格=fm.规格,型号=fm.型号,购建日期=fm.购建日期 ");
            sb.Append(" into #temp ");
            sb.Append(@"   
                from FIXED_Scrap fs ");
            sb.Append(@"   
                left join FIXED_Matetial fm on fm.编码=fs.资产编码 ");
            sb.Append(@"
                where 1=1 ");
            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", assetTypeID, sb);
            if ((assetTypeID.Length > 0) && (assetTypeID != null) && (assetTypeID != "-1") && (assetTypeID != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(assetTypeID); //分类编码list
                //string s1 = string.Join(",", typeIDList.ToArray()); //将list转为string，逗号分隔
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("fm.类型编码", s1, sb);
            }
            //资产编码筛选
            if ((materialID != null) && (materialID != "") && (materialID != "-1") && materialID != "--请选择--")
            {
                Guid MaterialID = new Guid(materialID);
                sb.Append(@" and fs.资产编码='" + MaterialID + "'");
            }
            WhereClauseUtility.AddStringEqual("fm.固定资产编码", assetID, sb);

            WhereClauseUtility.AddDateTimeGreaterThan("申请日期", DateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("申请日期", DateEnd, sb);

            sb.Append(@"
                order by 申请日期,资产名称 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #temp ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_ScrapInfo info = new FIXED_ScrapInfo();
                        info.BianMa = Convert.ToInt32(dr["编码"].ToString());
                        info.ZiChanBianMa = new Guid(dr["资产编码"].ToString());
                        info.ShenQingKeShi = dr["申请科室"].ToString();
                        info.ShenQingRiQi = Convert.ToDateTime(dr["申请日期"].ToString());
                        info.ShengChanGuo = dr["生产国"].ToString();
                        info.YuanZhi = dr["原值"].ToString();
                        info.CaiZhengGouJianZiJin = Convert.ToDouble(dr["财政购建资金"].ToString());
                        info.FeiCaiZhengGouJianZiJin = Convert.ToDouble(dr["非财政购建资金"].ToString());
                        info.LeiJiZheJiu = dr["累计折旧"].ToString();
                        info.ShenQingLiYou = dr["申请理由"].ToString();
                        info.ShenQingKeShiFuZeRen = dr["申请科室负责人"].ToString();

                        //基本信息
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.GuDingZiChanBianHao = dr["固定资产编号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());

                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsScrapDAL/GetScrapList", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="assetTypeID"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string assetTypeID)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + assetTypeID + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(dr["ID"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsScrapDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据编码获取报废数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_ScrapInfo GetScrapData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            //Guid Code1 = new Guid(Code);
            int Code1 = Convert.ToInt32(Code);
            sb.Append(@" select fs.* ");
            sb.Append(@" from FIXED_Scrap fs ");
            sb.Append(@" 
                        where 1=1 ");
            //sb.Append(" and 编码='" + Code1 + "'");
            sb.Append(" and 编码=" + Code1 + "");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_ScrapInfo()
                            {
                                BianMa = a.Field<int>("编码"),
                                ZiChanBianMa = a.Field<Guid>("资产编码"),
                                ShenQingKeShi = a.Field<string>("申请科室"),
                                ShenQingRiQi = a.Field<DateTime>("申请日期"),
                                ShengChanGuo = a.Field<string>("生产国"),
                                YuanZhi = a.Field<string>("原值"),
                                CaiZhengGouJianZiJin = a.Field<Double>("财政购建资金"),
                                FeiCaiZhengGouJianZiJin = a.Field<Double>("非财政购建资金"),
                                LeiJiZheJiu = a.Field<string>("累计折旧"),
                                ShenQingLiYou = a.Field<string>("申请理由"),
                                ShenQingKeShiFuZeRen = a.Field<string>("申请科室负责人"),

                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsScrapDAL/GetScrapData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增报废
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertEditPageData(FIXED_ScrapInfo info)
        {
            using (DataBase db = new DataBase(SqlHelper.MainConnectionString))
            {
                try
                {
                    //调用隐式事务 TransactionScope 
                    using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                    {

                        info.BianMa = GetMaxID("FIXED_Scrap") + 1;//获取表的编码最大值+1
                        //向固定资产报废记录表中插入一条数据
                        if (info != null)
                        {
                            db.FIXED_ScrapInfo.InsertOnSubmit(info);
                        }

                        //同时修改固定资产基本信息表中的是否报废字段

                        //取出固定资产基本信息实体
                        var FixedAssetsMaterialInfo = db.FIXED_MatetialInfo.SingleOrDefault<FIXED_MatetialInfo>(s => s.BianMa == info.ZiChanBianMa);
                        
                        //修改基本信息实体属性
                        FixedAssetsMaterialInfo.ShiFouBaoFei = true;

                        //执行更新操作
                        db.SubmitChanges();
                        ts.Complete(); //提交事务
                        return true;
                    }

                }
                catch (Exception ex)
                {
                    LogUtility.Error("FixedAssetsScrapDAL/InsertEditPageData", ex.ToString());
                    return false;
                }
            }
        }

        /// <summary>
        /// 获取表中编码最大值
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private int GetMaxID(string tablename)
        {
            int result = 0;
            StringBuilder sb = new StringBuilder();
            sb.Append(@" select 编码 from " + tablename + " where 编码 in (select max(cast(编码 as int)) from " + tablename + ") ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                if (dr.Read())
                {
                    result = Convert.ToInt32(dr["编码"]);
                }
                return result;
            }
        }

        /// <summary>
        /// 报废修改
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateEditPageData(FIXED_ScrapInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Scrap set ");
            sb.Append(@" 
                        申请日期='" + info.ShenQingRiQi + "', ");
            sb.Append(" 申请科室='" + info.ShenQingKeShi + "', ");
            sb.Append(" 生产国='" + info.ShengChanGuo + "', ");
            sb.Append(" 原值='" + info.YuanZhi + "', ");
            sb.Append(" 财政购建资金=" + info.CaiZhengGouJianZiJin + ", ");
            sb.Append(" 非财政购建资金=" + info.FeiCaiZhengGouJianZiJin + ", ");
            sb.Append(" 累计折旧='" + info.LeiJiZheJiu + "', ");
            sb.Append(" 申请理由='" + info.ShenQingLiYou + "', ");
            sb.Append(" 申请科室负责人='" + info.ShenQingKeShiFuZeRen + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= " + info.BianMa + "");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsScrapDAL/UpdateEditPageData", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 验证该资产是否已有报废单
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public bool IsHaveCurrentData(Guid code)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select * ");
            sb.Append(@"   
                from FIXED_Scrap fs ");
            sb.Append(@"
                where 1=1 ");
            sb.Append(@"
                and 资产编码='" + code + "'");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return ds.Tables[0].Rows.Count > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsScrapDAL/IsHaveCurrentData", ex.Message);
                return false;
            }
        }
    }
}
